﻿using PersistentObject.Common;
using PersistentObject.Common.Schema;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PersistentObject
{
    public class SqlExecutor
    {
        public POConnection Connection { get; internal set; }
        public IDbConnection DbConnection { get { return Connection.DbConnection; } }
        public BaseDialect Dialect { get { return Connection.Dialect; } }
        public IDbTransaction DbTransaction { get { return Connection.DbTransaction; } }

        public SqlExecutor(POConnection connection)
        {
            Connection = connection;
        }

        void OpenConnection()
        {
            bool wasClosed = DbConnection != null && DbConnection.State == ConnectionState.Closed;
            if (wasClosed) DbConnection.Open();
        }

        IDbCommand CreateCommand(string sql)
        {
            if (DbTransaction != null)
            {
                return Dialect.CreateCommand(DbTransaction, sql);
            }
            else
            {
                return Dialect.CreateCommand(DbConnection, sql);
            }
        }

        public int Count<T>(string sql, IEnumerable<IDataParameter> parameters)
        {
            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            if (parameters != null && parameters.Count() > 0)
            {
                foreach (var p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            IDataReader reader = command.ExecuteReader();
            reader.Read();
            var count = reader.GetInt32(0);
            reader.Close();
            return count;
        }

        public IEnumerable<T> Load<T>(string sql, IEnumerable<IDataParameter> parameters)
        {
            return Load<T>(sql, parameters, Dialect.SchemaHelper.DefaultDeep);
        }

        public IEnumerable<T> Load<T>(string sql, IEnumerable<IDataParameter> parameters, int deep)
        {
            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            if (parameters != null && parameters.Count() > 0)
            {
                foreach (var p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            IDataReader reader = command.ExecuteReader();

            ObjectLoader loader = new ObjectLoader(reader, Connection.Dialect, deep);
            IEnumerable<T> objects = loader.LoadObjects<T>();
            reader.Close();
            return objects;
        }

        public T Save<T>(string sql, T obj)
        {
            /* create command object
             * prepare parameters for the command
             * execute command
             * if key column is identity then get the returned identity key value
             * */
            Table table = Dialect.SchemaHelper.CreateTable<T>();
            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            Dialect.PrepareSaveParameters<T>(command, obj);
            IDataReader reader = command.ExecuteReader();

            if (table.KeyColumn.IsAutoGenerated)
            {
                var keyValue = Dialect.RetrieveKeyValue<T>(reader, command);
                if (keyValue != null && keyValue != DBNull.Value)
                {
                    if (keyValue.GetType() != table.KeyColumn.Property.PropertyType)
                    {
                        keyValue = Convert.ChangeType(keyValue, table.KeyColumn.Property.PropertyType);
                    }
                    table.KeyColumn.Property.SetValue(obj, keyValue);
                }
            }
            reader.Close();
            return obj;
        }

        public bool IsExisting<T>(string sql, T obj)
        {
            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            Dialect.PrepareExistingParameters<T>(command, obj);
            IDataReader reader = command.ExecuteReader();
            reader.Read();
            var result = reader.GetValue(0);
            reader.Close();
            var isExisting = 
                result is int && (int)result >= 1 ? true :
                result is uint && (uint)result >= 1 ? true :
                result is Int16 && (Int16)result >= 1 ? true :
                result is UInt16 && (UInt16)result >= 1 ? true :
                result is Int64 && (Int64)result >= 1 ? true :
                result is UInt64 && (UInt64)result >= 1 ? true :
                result is long && (long)result >= 1 ? true :
                result is ulong && (ulong)result >= 1 ? true :
                result is decimal && (decimal)result >= 1 ? true :
                result is bool && (bool)result == true ? true :
                false;
            return isExisting;
        }

        public T Update<T>(string sql, T obj)
        {
            return Save<T>(sql, obj);
        }

        public T Insert<T>(string sql, T obj)
        {
            return Save<T>(sql, obj);
        }

        public IEnumerable<T> Save<T>(string sql, IEnumerable<T> objects)
        {
            foreach (var obj in objects)
            {
                var r = Save<T>(sql, obj);
            }
            return objects;
        }

        public void Delete<T>(string sql, IEnumerable<IDataParameter> parameters)
        {
            Table table = Dialect.SchemaHelper.CreateTable<T>();

            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            if (parameters != null && parameters.Count() > 0)
            {
                foreach (var p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            IDataReader reader = command.ExecuteReader();
            reader.Close();
        }

        public T Delete<T>(string sql, T obj)
        {
            /* create command object
             * prepare parameters for the command
             * execute command
             * if key column is identity then get the returned identity key value
             * */
            Table table = Dialect.SchemaHelper.CreateTable<T>();
            OpenConnection();
            IDbCommand command = CreateCommand(sql);
            Dialect.PrepareDeleteParameters<T>(command, obj);
            IDataReader reader = command.ExecuteReader();
            //if (table.KeyColumn.IsIdentity)
            //{
            //    reader.Read();
            //    var keyValue = reader.GetValue(0);
            //    table.KeyColumn.Property.SetValue(obj, keyValue);
            //}
            reader.Close();
            return obj;
        }

        
    }
}
